package cn.ztuo.bitrade.dao;

import cn.ztuo.bitrade.entity.BourseProductManager;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QueryDslPredicateExecutor;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

public interface BourseProductManagerDao extends JpaRepository<BourseProductManager, String>, JpaSpecificationExecutor<BourseProductManager>, QueryDslPredicateExecutor<BourseProductManager> {

    BourseProductManager getById(Long id);

    BourseProductManager getByProductCode(String productCode);

    @Query(value = "SELECT m.id,m.username,count(o.order_id) as count,o.symbol FROM exchange_order o LEFT JOIN member m ON m.id = o.member_id WHERE 1=1 and (:symbol is null or o.symbol = :symbol) and (:endTime is null or o.time<= :endTime) and (:startTime is null or o.time>= :startTime) and ((:id is null ||m.id = :id) or (:userName is null or m.username LIKE :userName)) and m.id<>1 and m.id<>0 and o.status = 1 GROUP BY m.id,o.symbol ORDER BY count DESC LIMIT 0,100", nativeQuery=true)
    List<Object[]> exchangeHandRate(@Param("symbol") String symbol, @Param("startTime")Long start, @Param("endTime")Long end, @Param("userName")String name, @Param("id") Long id);

    @Query(value = "SELECT m.id,m.username,count(o.order_id) as count,o.symbol FROM contract_order o LEFT JOIN member m ON m.id = o.member_id WHERE 1=1 and (:symbol is null or o.symbol = :symbol) and  (:endTime is null or o.list_time<= :endTime) and (:startTime is null or o.list_time>= :startTime) and ((:id is null or m.id = :id) or (:userName is null or m.username LIKE :userName)) and m.id<>1 and m.id<>0 and o.status = 2 GROUP BY m.id,o.symbol ORDER BY count DESC LIMIT 0,100", nativeQuery=true)
    List<Object[]> contractHandRate(@Param("symbol") String symbol, @Param("startTime")Long start, @Param("endTime")Long end, @Param("userName")String name, @Param("id") Long id);

    @Query(value = "SELECT m.id,m.username,sum(o.amount) as count,o.symbol FROM exchange_order o LEFT JOIN member m ON m.id = o.member_id WHERE 1=1 and (:symbol is null or o.symbol = :symbol) and (:endTime is null or o.time<= :endTime) and (:startTime is null or o.time>= :startTime) and ((:id is null ||m.id = :id) or (:userName is null or m.username LIKE :userName)) and m.id<>1 and m.id<>0 and o.status = 1 GROUP BY m.id,o.symbol ORDER BY count DESC LIMIT 0,100", nativeQuery=true)
    List<Object[]> exchangeAmount(@Param("symbol") String symbol, @Param("startTime")Long start, @Param("endTime")Long end, @Param("userName")String name, @Param("id") Long id);

    @Query(value = "SELECT m.id,m.username,sum(o.amount) as count,o.symbol FROM contract_order o LEFT JOIN member m ON m.id = o.member_id WHERE 1=1 and (:symbol is null or o.symbol = :symbol) and (:endTime is null or o.list_time<= :endTime) and (:startTime is null or o.list_time>= :startTime) and ((:id is null or m.id = :id) or (:userName is null or m.username LIKE :userName)) and m.id<>1 and m.id<>0 and o.status = 2 GROUP BY m.id,o.symbol ORDER BY count DESC LIMIT 0,100", nativeQuery=true)
    List<Object[]> contractAmount(@Param("symbol") String symbol, @Param("startTime")Long start, @Param("endTime")Long end, @Param("userName")String name, @Param("id") Long id);

    @Query(value = "SELECT m.id,m.username,o.balance,o.coin_id FROM member_wallet o LEFT JOIN member m ON m.id = o.member_id WHERE 1=1 and (:symbol is null or o.coin_id =:symbol) ORDER BY o.balance DESC LIMIT 0,100", nativeQuery=true)
    List<Object[]> amountRank(@Param("symbol") String symbol);

    //--充币记录
    @Query(value = "SELECT m.id,m.username,sum(o.amount) balance ,o.unit coin_id FROM member_deposit o LEFT JOIN member m ON m.id = o.member_id WHERE 1=1 and  (:symbol is null or o.unit = :symbol) and (:endTime is null or o.create_time<=:endTime) and (:startTime is null or o.create_time>=:startTime) GROUP BY m.id,o.unit ORDER BY balance DESC LIMIT 0,100", nativeQuery=true)
    List<Object[]> chargeRank(@Param("symbol") String symbol,@Param("startTime")Date start, @Param("endTime")Date end);
    //--提币记录
    @Query(value = "SELECT m.id,m.username,sum(o.arrived_amount) balance,o.coin_id FROM withdraw_record o LEFT JOIN member m ON m.id = o.member_id WHERE 1=1 and (:symbol is null or o.coin_id = :symbol) and o.status = 3 and (:endTime is null or o.deal_time<=:endTime) and (:startTime is null or o.deal_time >= :startTime) GROUP BY m.id,o.coin_id ORDER BY balance DESC LIMIT 0,100", nativeQuery=true)
    List<Object[]> extractRank(@Param("symbol") String symbol, @Param("startTime") Date start, @Param("endTime")Date end);
}
